package com.itjob.scott.dao.impl;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.itjob.scott.dao.CallParameter;
import com.itjob.scott.dao.StudentDao;
import com.itjob.scott.entity.StudentEntity;

public class StudentDaoImpl implements StudentDao {

	private JdbcTemplate template;

	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}

	//匿名类RowMapper
	public List<StudentEntity> queryAll() {
		return template.query("select * from student_t",new RowMapper<StudentEntity>(){
			public StudentEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
				StudentEntity s= new StudentEntity();
				s.setsNo(rs.getString("S_NO"));
				s.setsName(rs.getString("S_NAME"));
				s.setsAge(rs.getInt("S_AGE"));
				s.setsSex(rs.getString("S_SEX"));
				return s;
			}
		});
		
	}

	

	public List<StudentEntity> queryAllSpring() {
		return template.query("select s_no sNo ,s_name sName,s_age sAge, s_sex sSex from student_t", 
				new BeanPropertyRowMapper<StudentEntity>(StudentEntity.class));
	}

	public StudentEntity queryByNo(String sNo,String sSex) {
		return template.queryForObject(
				"select s_no sNo ,s_name sName,s_age sAge, s_sex sSex from student_t where s_no=? and s_sex=?", 
				new Object[]{sNo,sSex},
				new BeanPropertyRowMapper<StudentEntity>(StudentEntity.class));
	}

	public void insert(StudentEntity entity) {
		//insert into student_t values(5,'李冰冰',30,'女')
		template.update("insert into student_t values(?,?,?,?)", new Object[]{
				entity.getsNo(),
				entity.getsName(),
				entity.getsAge(),
				entity.getsSex()
		});
		System.out.println("新增数据成功....");

	}
	
	public void update(StudentEntity entity) {
		//update student_t set s_age= 900 where s_no= '1'
		template.update("update student_t set s_age= ? where s_no= ?", new Object[]{
				entity.getsAge(),
				entity.getsNo()
		});
		System.out.println("修改成功....");
	}

	public void delete(String sNo) {
		//delete from student_t where s_no ='5'
		template.update("delete from student_t where s_no =?", new Object[]{
				sNo
		});
		System.out.println("删除数据成功....");
	}

	public Map<String, Object> callProcedure(final String callSql, final List<CallParameter> parameterList) {
		return template.execute(callSql, new CallableStatementCallback<Map<String, Object>>() {

			public Map<String, Object> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				
				List<CallParameter> outList = new ArrayList<CallParameter>();
				
				for(int i =0; i<parameterList.size();i++){
					CallParameter cp = parameterList.get(i);
					
					//给入参设值
					if(cp.getMode() == CallParameter.IN  || cp.getMode() == CallParameter.INOUT ){
						//cs.setString(1, a);
						cs.setObject(i+1, cp.getValue());
					}
					
					if(cp.getMode() == CallParameter.INOUT  || cp.getMode() == CallParameter.OUT ){
						//cs.registerOutParameter(3, Types.VARCHAR); //设置s的值
						//list.add(new CallParameter("s","赵敏",CallParameter.INOUT,Types.VARCHAR));
						cs.registerOutParameter(i+1, cp.getType());
						cp.setReturnIndex(i+1);
						outList.add(cp);
					}
					
				}
				
				cs.executeUpdate();
				
				if(!outList.isEmpty()){
					Map<String, Object> out  = new HashMap<String, Object>();
					
					for (CallParameter c : outList) {
						//cs.getString(3)
						out.put(c.getName(), cs.getObject(c.getReturnIndex()));
					}
					return out;
				}
				
				return null;
			}
		});
	}

}
